package com.erp.app.catalog.category.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.commons.beanutils.MethodUtils;

import com.erp.app.catalog.category.bizLogic.CategoryCommonUtil;
import com.erp.app.catalog.category.criteria.CategoryItemCategoryCriteria;
import com.erp.app.catalog.category.criteria.CategoryItemClassCriteria;
import com.erp.app.catalog.category.criteria.CategoryItemLineCriteria;

import com.erp.app.catalog.category.vo.CategoryItemCategoryVO;
import com.erp.app.catalog.category.vo.CategoryItemClassVO;
import com.erp.app.catalog.category.vo.CategoryItemLineVO;

import com.erp.app.common.ProjectConstants;

import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

public class CategoryCommonDao {
	CategoryCommonUtil util = new CategoryCommonUtil();
	
	
	public static String getCategoryItemLineSqlQuery(SimpleBaseSrchCriteria obj) {
		CategoryItemLineCriteria criteria = (CategoryItemLineCriteria)obj;
		String sql = "";
		
		String whereClause = categoryItemLineToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";
		sql = " SELECT a.COMPANY_CODE, a.NODE_ID ITEM_LINE, a.DESCRIPTION" 
			+ " FROM T_CATALOG a "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		Logger.log(sql);
		
		return sql;	
	}
	
	public static SimpleBaseVo getCategoryItemLineSearchResultSetValues(ResultSet rs) throws SQLException {
		CategoryItemLineVO vo = new CategoryItemLineVO();
		vo.setCompanyCode(rs.getString("COMPANY_CODE"));				
		vo.setItemLine(rs.getString("ITEM_LINE"));				
		vo.setItemLineDesc(rs.getString("DESCRIPTION"));
		return vo;
	}
	
	public static String categoryItemLineToWhereClause(SimpleBaseSrchCriteria obj) {
		StringBuffer whereClause = new StringBuffer();
		CategoryItemLineCriteria criteria = (CategoryItemLineCriteria)obj;
		
		whereClause.append(" WHERE a.CATALOG_LEVEL = 1 "
				+ " AND a.BROWSER = '" + ProjectConstants.CATALOG_BWR_SHOW + "' ");
		if (!CommonUtil.isEmpty(criteria.getItemLine())) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append(" a.NODE_ID = '" + criteria.getItemLine() + "' ");
		}
		if (!CommonUtil.isEmpty(criteria.getStatus())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" a.STATUS = '" + criteria.getStatus() + "' ");
        }
		if(!CommonUtil.isEmpty(criteria.getUserCode())){
    		whereClause.append(" AND NOT EXISTS (SELECT 1 FROM  T_CATALOG_SECURITY_DTL b WHERE b.USER_CODE = " +
    			"'" + criteria.getUserCode()+"' AND  b.NODE_ID = a.NODE_ID ) ");
    	}
		
		return whereClause.toString();
	}
	
	public static String getCategoryItemCategorySqlQuery(SimpleBaseSrchCriteria obj) {
		CategoryItemCategoryCriteria criteria = (CategoryItemCategoryCriteria)obj;
		String sql = "";
		
		String whereClause = categoryItemCategoryToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";
		sql = " SELECT a.COMPANY_CODE, b.NODE_ID ITEM_LINE, a.NODE_ID ITEM_CATEGORY, a.DESCRIPTION"
			+ " FROM T_CATALOG a, T_CATALOG_CHILDREN b"
			+ whereClause
			+ groupByClause
			+ orderByClause;
		Logger.log(sql);
		
		return sql;	
	}
	public static SimpleBaseVo getCategoryItemCategorySearchResultSetValues(ResultSet rs) throws SQLException {
		CategoryItemCategoryVO vo = new CategoryItemCategoryVO();
		vo.setCompanyCode(rs.getString("COMPANY_CODE"));				
		vo.setItemLine(rs.getString("ITEM_LINE"));				
		vo.setItemCategory(rs.getString("ITEM_CATEGORY"));
		vo.setCategoryItemCategoryDesc(rs.getString("DESCRIPTION"));
		return vo;
	}
	
	public static String categoryItemCategoryToWhereClause(SimpleBaseSrchCriteria obj) {
		StringBuffer whereClause = new StringBuffer();
		
		CategoryItemCategoryCriteria criteria = (CategoryItemCategoryCriteria)obj;
		whereClause.append(" WHERE a.CATALOG_LEVEL = 2 "
				+ " AND a.BROWSER = '" + ProjectConstants.CATALOG_BWR_SHOW + "' "
				+ " AND a.NODE_ID = b.CHILD_NODE_ID ");
		if (!CommonUtil.isEmpty(criteria.getItemLine())) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append(" b.NODE_ID = '" + criteria.getItemLine() + "' ");
		}
		if (!CommonUtil.isEmpty(criteria.getItemCategory())) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append(" a.NODE_ID = '" + criteria.getItemCategory() + "' ");
		}
		if (!CommonUtil.isEmpty(criteria.getStatus())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" a.STATUS = '" + criteria.getStatus() + "' ");
        }		
		if(!CommonUtil.isEmpty(criteria.getUserCode())){
    		whereClause.append(" AND NOT EXISTS (SELECT 1 FROM  T_CATALOG_SECURITY_DTL b WHERE b.USER_CODE = " +
    			"'" + criteria.getUserCode()+"' AND  b.NODE_ID = a.NODE_ID ) ");
    	}
		
		return whereClause.toString();  
	}
	
	public static String getCategoryItemClassSqlQuery(SimpleBaseSrchCriteria obj) {
		CategoryItemClassCriteria criteria = (CategoryItemClassCriteria)obj;
		String sql = "";
		
		String whereClause = categoryItemClassToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";
		sql = " SELECT a.COMPANY_CODE, b.NODE_ID ITEM_LINE, c.NODE_ID ITEM_CATEGORY" 
			+ ", a.NODE_ID ITEM_CLASS, a.DESCRIPTION ITEM_CLASS_DESC " 
			+ " FROM T_CATALOG a, T_CATALOG_CHILDREN b, T_CATALOG_CHILDREN c "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		Logger.log(sql);
		
		return sql;	
	}
	public static SimpleBaseVo getCategoryItemClassSearchResultSetValues(ResultSet rs) throws SQLException {
		CategoryItemClassVO vo = new CategoryItemClassVO();
		
		vo.setCompanyCode(rs.getString("COMPANY_CODE"));				
		vo.setItemLine(rs.getString("ITEM_LINE"));				
		vo.setItemCategory(rs.getString("ITEM_CATEGORY"));
		vo.setItemClass(rs.getString("ITEM_CLASS"));
		vo.setClassItemClassDesc(rs.getString("ITEM_CLASS_DESC"));
		
		return vo;
	}
	
	public static String categoryItemClassToWhereClause(SimpleBaseSrchCriteria obj) {
		StringBuffer whereClause = new StringBuffer();
		
		CategoryItemClassCriteria criteria = (CategoryItemClassCriteria)obj;
		whereClause.append(" WHERE a.CATALOG_LEVEL = 3 "
				+ " AND a.BROWSER = '" + ProjectConstants.CATALOG_BWR_SHOW + "' "
				+ " AND a.NODE_ID = c.CHILD_NODE_ID AND b.CHILD_NODE_ID = c.NODE_ID ");
		if (!CommonUtil.isEmpty(criteria.getItemLine())) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append(" b.NODE_ID = '" + criteria.getItemLine() + "' ");
		}
		if (!CommonUtil.isEmpty(criteria.getItemCategory())) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append(" c.NODE_ID = '" + criteria.getItemCategory() + "' ");
		}
		if (!CommonUtil.isEmpty(criteria.getItemClass())) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append(" a.NODE_ID = '" + criteria.getItemClass() + "' ");
		}
		if (!CommonUtil.isEmpty(criteria.getStatus())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" a.STATUS = '" + criteria.getStatus() + "' ");
        }	
		if(!CommonUtil.isEmpty(criteria.getUserCode())){
    		whereClause.append(" AND NOT EXISTS (SELECT 1 FROM  T_CATALOG_SECURITY_DTL b WHERE b.USER_CODE = " +
    			"'" + criteria.getUserCode()+"' AND  b.NODE_ID = a.NODE_ID ) ");
    	}
		
		return whereClause.toString();  
	}
	
//	public static String getSqlQuery(SimpleBaseSrchCriteria obj) {
//		String sql="";
//		if (obj.getClass().equals(CategoryItemLineCriteria.class)){
//			sql = getCategoryItemLineSqlQuery(obj);
//		}else if (obj.getClass().equals(CategoryItemCategoryCriteria.class)){
//			sql = getCategoryItemCategorySqlQuery(obj);
//		}else if (obj.getClass().equals(CategoryItemClassCriteria.class)){
//			sql = getCategoryItemClassSqlQuery(obj);
//		}		
//		return sql;
//	}
//	
//	public static SimpleBaseVo getSearchResultSetValues(ResultSet rs, SimpleBaseVo obj) throws SQLException {
//		SimpleBaseVo vo = null;
//		if (obj.getClass().equals(CategoryItemLineVO.class)){
//			vo = getCategoryItemLineSearchResultSetValues(rs);
//		}else if (obj.getClass().equals(CategoryItemCategoryVO.class)){
//			vo = getCategoryItemCategorySearchResultSetValues(rs);
//		}else if (obj.getClass().equals(CategoryItemClassVO.class)){
//			vo = getCategoryItemClassSearchResultSetValues(rs);
//		}
//		return vo;
//	}
//	
//	public static SimpleBaseVo getSearchResultSetValues(ResultSet rs) throws SQLException {
//		SimpleBaseVo vo = getCategoryItemLineSearchResultSetValues(rs);		
//		return vo;
//	}
	
	public Collection findByCriteria(Object obj, SimpleBaseVo model, Connection conn) throws FindRecordException {

        SimpleBaseSrchCriteria criteria = (SimpleBaseSrchCriteria) obj;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList rslt = new ArrayList();
        
        String criteriaClassName = criteria.getClass().toString();
        String getSqlQueryMethorName = "get"
			+ criteriaClassName.substring(criteriaClassName.lastIndexOf(".")+1,criteriaClassName.length()-8)
			+"SqlQuery";        
        
        String modelVoClassName = model.getClass().toString();        
        String getResultMethorName = "get"
        	+ modelVoClassName.substring(modelVoClassName.lastIndexOf(".")+1,modelVoClassName.length()-2)
        	+"SearchResultSetValues";

        try {
        	String query = (String)MethodUtils.invokeMethod(this,getSqlQueryMethorName,criteria);
            stmt = conn.createStatement();
            stmt.setMaxRows(ProjectConstants.DB_ROW_NUM);
            rs = stmt.executeQuery(query);

            if (rs != null) {
                while (rs.next()) {
                	rslt.add(MethodUtils.invokeMethod(this,getResultMethorName,rs));
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
            throw new FindRecordException(e.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }
	
	public Collection findByCriteria(Object obj, Connection conn) throws FindRecordException {
		SimpleBaseSrchCriteria criteria = (SimpleBaseSrchCriteria) obj;
        ArrayList rslt = new ArrayList();

        try{
        	rslt = (ArrayList)this.findByCriteria(criteria
        						,(SimpleBaseVo)Class.forName(criteria.getVOClassName()).newInstance(),conn);
        }catch(Exception e){
        	Logger.log(e.toString());
        }
        return rslt;
	}	
}
